﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Register.IDAL;
using Register.Model;
using System.Data.SqlClient;
using System.Data;
using Register.DBUtility;
namespace Register.DAL
{
    /// <summary> 
    /// FileName: ProvinceOrCityDAL.cs    
    /// CLRVersion: 4.0.30319.269    
    /// Author: DoubleZ    
    /// Corporation:     
    /// Description:     
    /// DateTime: 2012/6/28 20:01:08    
    /// </summary>
   public  class ProvinceOrCityDAL:IProvinceOrCityDAL
    {
        /// <summary>
        /// 插入城市信息
        /// </summary>
        /// <param name="city"></param>
        /// <returns></returns>
    public   int  InsertCityInfo(City city) {
        StringBuilder strSql = new StringBuilder();
        strSql.Append("insert into System_City(");
        strSql.Append("City_ID,Province_ID,CIty_Name,City_AddTime,City_Recoder)");
        strSql.Append(" values (");
        strSql.Append("@City_ID,@Province_ID,@CIty_Name,@City_AddTime,@City_Recoder)");
        SqlParameter[] parameters = {
					new SqlParameter("@City_ID", SqlDbType.Char,5),
					new SqlParameter("@Province_ID", SqlDbType.Char,5),
                    new SqlParameter("@CIty_Name", SqlDbType.Char,10),
					new SqlParameter("@City_AddTime", SqlDbType.DateTime),
					new SqlParameter("@City_Recoder", SqlDbType.VarChar,20)
                                    };

        parameters[0].Value = city.City_ID;
        parameters[1].Value = city.Province_ID;
        parameters[2].Value = city.CIty_Name;
        parameters[3].Value = city.City_AddTime;
        parameters[4].Value = city.City_Recoder;

        return (int)DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }
       /// <summary>
       /// 获取省区信息，绑定前端下拉列表
       /// </summary>
       /// <returns></returns>
 public    List<Province> GetProvinceList() {

     String sql = " select Province_ID,Province_Name  from System_province";
     List<Province> list = new List<Province>();
     SqlDataReader reader = DbHelperSQL.ExecuteReader(sql);
     list = ChangeReaderToList(reader);
     reader.Close();
     return list;
    }
 public List<City> GetCityList() {
     String sql = " select City_ID,CIty_Name  from System_City where Province_ID in (select  Province_ID from System_province)";
     List<City> list = new List<City>();
     SqlDataReader reader = DbHelperSQL.ExecuteReader(sql);
     list = ChangeReaderToCityList(reader);
     reader.Close();
     return list;
 }
 private List<City> ChangeReaderToCityList(SqlDataReader reader)
 {
     List<City> list = new List<City>();
     while (reader.Read())
     {
         City paramter = new City();
         paramter.City_ID = reader.GetString(0);
         paramter.CIty_Name= reader.GetString(1);
         list.Add(paramter);
     }
     return list;
 }
 private List<Province> ChangeReaderToList(SqlDataReader reader)
 {
     List<Province> list = new List<Province>();
     while (reader.Read())
     {
         Province paramter = new Province();
         paramter.Province_ID= reader.GetString(0);
         paramter.Province_Name = reader.GetString(1);
         list.Add(paramter);
     }
     return list;
 }

        /// <summary>
        /// 插入省信息
        /// </summary>
        /// <param name="province"></param>
        /// <returns></returns>
    public int  InsertProvinceInfo(Province province) {
        StringBuilder strSql = new StringBuilder();
        strSql.Append("insert into System_province(");
        strSql.Append("Province_ID,Province_Name,Province_AddTime,Province_Recoder)");
        strSql.Append(" values (");
        strSql.Append("@Province_ID,@Province_Name,@Province_AddTime,@Province_Recoder)");
        SqlParameter[] parameters = {
					new SqlParameter("@Province_ID", SqlDbType.Char,5),
					new SqlParameter("@Province_Name", SqlDbType.Char,10),
					new SqlParameter("@Province_AddTime", SqlDbType.DateTime),
					new SqlParameter("@Province_Recoder", SqlDbType.VarChar,20)
                                    };

        parameters[0].Value = province.Province_ID;
        parameters[1].Value = province.Province_Name;
        parameters[2].Value = province.Province_AddTime;
        parameters[3].Value = province.Province_Recoder;

        return (int)DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
    }
       /// <summary>
       /// 根据城市ID获取城市名
       /// </summary>
       /// <param name="CityID"></param>
       /// <returns></returns>
 public    string GetCityName(string CityID) {
     string strSql = "select CIty_Name from System_City  where  City_ID='" + CityID + "'";
         DataSet ds=DbHelperSQL.Query(strSql);
            string CItyName = "无";
            if (ds != null)
            {
                CItyName = ds.Tables[0].Rows[0]["CIty_Name"].ToString();

            }
            return  CItyName;
        }
       /// <summary>
       /// 根据城市ID获取省名,此处CityID即为医院表中的City_ID字段
       /// </summary>
       /// <param name="CityID"></param>
       /// <returns></returns>
 public   string GetProvinceName(string CityID)
 {

     string strSql = "select Province_Name from System_province  where Province_ID in (select  Province_ID  from System_City where City_ID='" + CityID + "')";
     DataSet ds = DbHelperSQL.Query(strSql);
     string ProvinceName = "无";
     if (ds != null)
     {
         ProvinceName = ds.Tables[0].Rows[0]["Province_Name"].ToString();

     }
     return ProvinceName;
 }
    }
          
    }

